// 简易的excel操作
package excel

import (
	"encoding/json"
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize"
	"strconv"
)

// 暂不支持流式写入，效率低
type Excel360 struct {
	file            *excelize.File
	sheetWords      []string
	sheetName       string
	fileName        string
	headerNameArray string
}

// 初始化一个file对象
func NewExcel() *Excel360 {
	rval := &Excel360{
		file: excelize.NewFile(),
		sheetWords: []string{
			"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U",
			"V", "W", "X", "Y", "Z",
		},
		sheetName: "Sheet1",
	}
	return rval
}

// 设置表格页名
func (this *Excel360) SetSheetName(SheetName string) {

	this.sheetName = SheetName
	this.file.NewSheet(this.sheetName)

}

// 设置表头，需要传入表头数组
func (this *Excel360) SetHeader(headerNameArray []string) {
	for k, v := range headerNameArray {
		this.file.SetCellValue(this.sheetName, this.sheetWords[k]+"1", v)
	}
}

// 设置表头居中样式，需要传入表头数组
func (this *Excel360) SetHeadStyle(headerNameArray []string) {
	//设置列的宽度
	this.file.SetColWidth(this.sheetName, "A", this.sheetWords[len(headerNameArray)-1], 18)
	headStyleID, _ := this.file.NewStyle(`{
	  "font":{
	     "color":"#333333",
	     "bold":false,
	     "family":"arial"
	  },
	  "alignment":{
	     "vertical":"center",
	     "horizontal":"center"
	  }
	}`)
	//设置表头的样式
	this.file.SetCellStyle(this.sheetName, "A1", this.sheetWords[len(headerNameArray)-1]+"1", headStyleID)
}

// 为每个单元循环写入数据，不包括表头
func (this *Excel360) SetData(data [][]string) {
	line := 1
	// 循环写入数据
	for kk, v := range data {
		if kk == 0 {
			continue
		}
		line++
		for kkk, _ := range data[kk] {
			this.file.SetCellValue(this.sheetName, this.sheetWords[kkk]+strconv.Itoa(line), v[kkk])

		}
	}
}

// 保存excel
func (this *Excel360) SaveExcel(fileName string) error {
	if err := this.file.SaveAs(fileName + ".xlsx"); err != nil {
		return err
	}
	return nil
}

// 快速创建excel,data为要写入的数据,fileName 文件名称
func (this *Excel360) CreateXlS(data [][]string, fileName string) error {

	if this.sheetName != "Sheet1" {

		this.file.DeleteSheet("Sheet1")
	}

	this.SetHeader(data[0])

	//设置列的宽度和表头居中
	this.SetHeadStyle(data[0])

	// 循环写入数据
	this.SetData(data)

	// 保存文件
	err := this.SaveExcel(fileName)
	if err != nil {
		return err
	}
	return nil
}

// 根据Json对象数组创建excel
func (this *Excel360) CreateXlSByJson(jsonDate string, fileName string) error {
	arr, err := JsonToArr(jsonDate)
	if err != nil {
		return err
	}
	err = this.CreateXlS(arr, fileName)

	if err != nil {
		return err
	}

	return nil
}

// Json转二维数组
func JsonToArr(jsonData string) ([][]string, error) {
	var rawData []map[string]interface{}
	err := json.Unmarshal([]byte(jsonData), &rawData)
	if err != nil {
		//fmt.Println("Error decoding JSON:", err)
		return nil, err
	}

	// 获取所有可能的字段
	var allFields []string
	for _, row := range rawData {
		for field := range row {
			if !contains(allFields, field) {
				allFields = append(allFields, field)
			}
		}
	}

	// 构建二维数组，将数据转为字符串
	var data [][]string
	data = append(data, allFields)
	for _, row := range rawData {
		var rowData []string
		for _, field := range allFields {
			// 如果字段存在于当前行，则转为字符串，否则为空字符串
			value, exists := row[field]
			if exists {
				rowData = append(rowData, fmt.Sprintf("%v", value))
			} else {
				rowData = append(rowData, "")
			}
		}
		data = append(data, rowData)
	}
	return data, nil
}

func contains(s []string, e string) bool {
	for _, a := range s {
		if a == e {
			return true
		}
	}
	return false
}

// 创建excel示例，循环cell单元写入，效率低
func ExampleCreateXlS() {

	//要写入的数据
	data := [][]string{
		{"name", "age"},
		{"John", "12", "aaa"},
		{"Jane", "14", "bbb"},
		{"Bob", "13", "ddd"},
	}
	//创建excel的file对象
	file := NewExcel()
	//输出文件到lalala.excel中
	file.CreateXlS(data, "lalala")
}
